*-------------------------------------------------------------------------------------------------------*
*RESEARCHERS:		Mette Foged, Linea Hasager and Vasil Yasenov
*PROJECT:			Meta-analysis
*DESCRIPTION:		1  Create appendix table 1 listing papers (author, year, countries, etc.)
*-------------------------------------------------------------------------------------------------------*


*Countries in each paper
clear all
set more off

use "${data}\database_clean.dta", replace

replace country="U.S. and Canada" if country=="United States, Canada"
replace country="U.S." if country=="United States"
replace country="U.K." if country=="United Kingdom"

egen id=group(paper_id country)
order id

duplicates drop id, force
keep country paper_id id

bysort paper_id: gen n=_n

drop id

reshape wide country, i(paper_id) j(n)

replace country1=country1 + ", " + country2 if country2 != ""
replace country1=country1 + ", " + country3 if country3 != ""

rename country1 countries
drop country2 country3

save "${data}\n_countries.dta", replace


*Types of variation in each paper
clear all
set more off

use "${data}\database_clean.dta", replace


egen id=group(paper_id variation_org)
order id

duplicates drop id, force
keep variation_org paper_id id

bysort paper_id: gen n=_n

drop id

reshape wide variation_org, i(paper_id) j(n)

replace variation_org1=variation_org1 + "; " + variation_org2 if variation_org2 != ""
replace variation_org1=variation_org1 + "; " + variation_org3 if variation_org3 != ""
replace variation_org1=variation_org1 + "; " + variation_org4 if variation_org4 != ""

rename variation_org1 variation
drop variation_org2 variation_org3 variation_org4

save "${data}\n_variation.dta", replace


*Types of empirical strategies in each paper
clear all
set more off

use "${data}\database_clean.dta", replace

*Create categories for IV
gen		 	IV_type="IV (Bartik Type)" if IV=="bartik_type"
replace 	IV_type="IV (Other)" if (IV=="other" | IV=="lagged_inflow")
replace 	IV_type="OLS" if IV=="" & naturalexperiment!=1
replace		IV_type="Natural Experiment" if naturalexperiment==1 & IV==""
replace		IV_type="IV (Natural Experiment)" if naturalexperiment==1 & IV!=""
ta IV_type


egen id=group(paper_id IV_type)
order id

duplicates drop id, force
keep IV_type paper_id id

bysort paper_id: gen n=_n

drop id

reshape wide IV_type, i(paper_id) j(n)

replace IV_type1=IV_type1 + "; " + IV_type2 if IV_type2 != ""
replace IV_type1=IV_type1 + "; " + IV_type3 if IV_type3 != ""

rename IV_type1 IV_type
drop IV_type2 IV_type3

save "${data}\n_IV_type.dta", replace



*Full data period in each paper
clear all
set more off


use "${data}\database_clean.dta", replace

replace country="U.S. and Canada" if country=="United States, Canada"
replace country="U.S." if country=="United States"
replace country="U.K." if country=="United Kingdom"

egen id=group(paper_id data_period)
order id

duplicates drop id, force
keep data_period paper_id 

gen firsty=substr(data_period,1,4)
gen lasty=substr(data_period,6,4)
destring firsty lasty, replace

sort paper_id firsty
bysort paper_id: egen f=min(firsty)
sort paper_id lasty
bysort paper_id: egen l=max(lasty)

tostring f l, force replace

gen full_period=f
replace full_period= f + "-" + l if l !="."
keep paper_id full_period
duplicates drop paper_id, force


save "${data}\n_dataperiod.dta", replace





*Wages
clear all
set more off


use "${data}\database_clean.dta", replace

replace country="U.S. and Canada" if country=="United States, Canada"
replace country="U.S." if country=="United States"
replace country="U.K." if country=="United Kingdom"

set more off

keep if wage==1

collapse (count) n_wage=estimate , by(authors year journal journal_ranking paper_id)

sort authors year journal_ranking
format n_wage %4.0f

label variable authors "Authors"
label variable n_wage "No. estimates (wage)"
label variable year "Year of Publication"
label variable journal_ranking "Journal Ranking"


list authors year journal n_wage
save "${data}/n_wage.dta", replace


*Employemnt
clear all
set more off


use "${data}\database_clean.dta", replace

replace country="U.S. and Canada" if country=="United States, Canada"
replace country="U.S." if country=="United States"
replace country="U.K." if country=="United Kingdom"

keep if empl==1

collapse (count) n_empl=estimate , by(authors year journal journal_ranking paper_id)

sort authors year journal_ranking
format n_empl %4.0f

label variable authors "Authors"
label variable n_empl "No. estimates (employment)"
label variable year "Year of Publication"
label variable journal_ranking "Journal Ranking"


merge m:m paper_id using "${data}/n_wage.dta"
sort paper_id
drop _merge 

merge m:m paper_id using "${data}/n_countries.dta"
sort paper_id
drop _merge 

merge m:m paper_id using "${data}/n_variation.dta"
sort paper_id
drop _merge 

merge m:m paper_id using "${data}/n_IV_type.dta"
sort paper_id
drop _merge 

merge m:m paper_id using "${data}/n_dataperiod.dta"
sort paper_id
drop _merge 

list authors year journal n_wage n_empl countries full_period variation IV_type

*Rename some journals
replace journal="The B.E. Journal of Economic Analysis \& Policy" if journal=="The B.E. Journal of Economic Analysis & Policy"

*Abbreviate to et al. for more than 2 authors
replace authors="Borjas et al." if authors=="Borjas, Freeman, Katz, DiNardo and Abowd"
replace authors="Bratsberg et al." if authors=="Bratsberg, Raaum, Røed and Schøne"
replace authors="Brücker et al." if authors=="Brücker, Hauptmann, Jahn and Upward"
replace authors="Ceritoglu et al." if authors=="Ceritoglu, Yunculer, Torun and Tumen"
replace authors="Malchow-Møller et al." if authors=="Malchow-Møller, Munch, Seidelin and Skaksen"
replace authors="Barrett et al." if authors=="Barrett, Bergin and Duffy"
replace authors="Barrett et al." if authors=="Barrett, Bergin and Kelly"
replace authors="Bauer et al." if authors=="Bauer, Flake and Sinning"
replace authors="Borjas et al." if authors=="Borjas, Freeman and Katz"
replace authors="Brunelloa et al." if authors=="Brunelloa, Lodigiani and Rocco"
replace authors="Carrasco et al." if authors=="Carrasco, Jimeno and Ortega"
replace authors="Cattaneo et al." if authors=="Cattaneo, Fiorio and Peri"
replace authors="D'Amuri et al." if authors=="D'Amuri, Ottaviano and Peri"
replace authors="Docquier et al." if authors=="Docquier, Ozden and Peri"
replace authors="Dustmann et al." if authors=="Dustmann, Fabbri and Preston"
replace authors="Dustmann et al." if authors=="Dustmann, Frattini and Preston"
replace authors="Dustmann et al." if authors=="Dustmann, Schönberg and Stuhler"
replace authors="Gavosto et al." if authors=="Gavosto, Venturini and Villosio"
replace authors="Greenwood et al." if authors=="Greenwood, Hunt and Kohli"
replace authors="Hothckiss et al." if authors=="Hotchkiss, Quispe-Agnoli and Rios-Avila"
replace authors="Malchow-Møller et al." if authors=="Malchow-Møller, Munch and Skaksen"
replace authors="Manacorda" if authors=="Manacorda, Manning and Wadsworth"
replace authors="Mitaritonna et al." if authors=="Mitaritonna, Orefice and Peri"


*Make author names compatible with latex
replace authors=`"Bratsberg, Raaum, R\o ed and Sch\o ne"' if authors=="Bratsberg, Raaum, Røed and Schøne"
replace authors=`"Br{\"u}cker and Jahn"' if authors=="Brücker and Jahn"
replace authors=`"Br{\"u}cker, Hauptmann, Jahn and Upward"' if authors=="Brücker, Hauptmann, Jahn and Upward"
replace authors="D'Amuri and Peri" if authors=="D'amuri and Peri"
replace authors=`"Dustmann, Sch{\"o}nberg and Stuhler"' if authors=="Dustmann, Schönberg and Stuhler"
replace authors="Gonz{\'a}lez and Ortega" if authors=="González and Ortega"
replace authors="Malchow-M\o ller, Munch and Skaksen" if authors=="Malchow-Møller, Munch and Skaksen"
replace authors=`"Malchow-M\o ller, Munch, Seidelin and Skaksen"' if authors=="Malchow-Møller, Munch, Seidelin and Skaksen"
replace authors=`"Winter-Ebmer and Zweim{\"u}ller"' if authors=="Winter-Ebmer and Zweimüller"
replace authors=`"Malchow-M\o ller et al."' if authors=="Malchow-Møller et al."
replace authors=`"Br{\"u}cker et al."' if authors=="Brücker et al."


*Convert to strings
foreach var in n_wage n_empl {
tostring `var', replace force 
}

foreach var in n_wage n_empl {
replace `var'="-" if `var'=="." 
}


*Change countries Llull (2017)
replace authors="Llull" if authors=="Llull "
replace countries="Canada, U.S." if authors=="Llull" & year==2017 & countries=="U.S. and Canada, U.S."


preserve
*Delete if no wage effects (only employment effect papers are deleted from this table)
drop if n_wage=="-"

*Output table to latex	
listtex authors year journal countries full_period n_wage variation ///
using "${out}/appendix_table1.tex", ///
rstyle(tabular) replace 
restore	


*Output table to latex	
listtex authors year journal countries full_period n_wage n_empl variation IV_type ///
using "${out}/oappendix_table7.tex", ///
rstyle(tabular) replace 
	
*Delete temporary data
erase "${data}/n_wage.dta"
erase "${data}/n_countries.dta"
erase "${data}/n_variation.dta"
erase "${data}/n_IV_type.dta"
erase "${data}/n_dataperiod.dta"


